#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
线损计算定时任务
功能：
1. 每天凌晨3:30执行（在数据冻结任务02:00之后）
2. 获取所有在线RTU列表
3. 计算每个RTU的线损情况（昨天的数据）
4. 保存计算结果到 dev_rtu_line_loss 表
"""
import logging
import os
import sys
from datetime import date, timedelta
from typing import List, Dict, Any

import pymysql
import tenacity

# 添加项目根目录到 Python 路径
sys.path.insert(0, os.path.abspath(os.path.join(os.path.dirname(__file__), '..')))

from common.config import settings

# ============ 日志配置 ============
# 确保日志目录存在
log_dir = os.path.join(os.path.dirname(__file__), '..', 'logs')
os.makedirs(log_dir, exist_ok=True)

logging.basicConfig(
	level=logging.INFO,
	format="%(asctime)s [%(levelname)s] %(message)s",
	handlers=[
		logging.StreamHandler(sys.stdout),
		logging.FileHandler(os.path.join(log_dir, "line_loss_calculation.log"), encoding='utf-8')
	]
)
logger = logging.getLogger(__name__)

# ============ 数据库配置（从统一配置对象获取） ============
DB_HOST = settings.db_host
DB_PORT = settings.db_port
DB_USER = settings.db_user
DB_PASSWORD = settings.db_password
DB_NAME = settings.db_name


class LineLossCalculationTask:
	"""线损计算任务类"""

	def __init__(self):
		self.conn = None
		self.calc_date = None
		self.stats = {
			"total_rtu": 0,
			"success": 0,
			"no_data": 0,
			"error": 0
		}

	def connect_db(self):
		"""连接数据库"""
		if not all([DB_HOST, DB_USER, DB_PASSWORD, DB_NAME]):
			raise ValueError("数据库配置不完整，请检查环境变量")

		self.conn = pymysql.connect(
			host=DB_HOST,
			port=DB_PORT,
			user=DB_USER,
			password=DB_PASSWORD,
			database=DB_NAME,
			charset='utf8mb4',
			autocommit=False
		)
		logger.info(f"数据库连接成功: {DB_HOST}:{DB_PORT}/{DB_NAME}")

	def close_db(self):
		"""关闭数据库连接"""
		if self.conn:
			self.conn.close()
			logger.info("数据库连接已关闭")

	def get_all_rtus(self) -> List[Dict[str, Any]]:
		"""
		获取所有RTU设备列表（不限制状态）

		Returns:
			List[Dict]: RTU列表，包含 id, serial_no
		"""
		sql = """
            SELECT id, serial_no
            FROM dev_rtu
            WHERE deleted = 0 AND status = 'ONLINE' AND collector_type = 'Master'
            ORDER BY id
        """
		with self.conn.cursor(pymysql.cursors.DictCursor) as cursor:
			cursor.execute(sql)
			rows = cursor.fetchall()
			logger.info(f"查询到 {len(rows)} 个RTU设备")
			return list(rows)

	def get_Master_consumption(self, serial_no: str, calc_date: date) -> float:
		"""
		获取三相表当日用电量（从DC_METER_FREEZE表）

		Args:
			serial_no: RTU设备编码
			calc_date: 计算日期

		Returns:
			float: RTU用电量，无数据返回0
		"""
		sql = """
            SELECT daily_kwh
            FROM dc_meter_freeze
            WHERE serial_no = %s
              AND freeze_day = %s
        """
		with self.conn.cursor(pymysql.cursors.DictCursor) as cursor:
			cursor.execute(sql, (serial_no, calc_date))
			row = cursor.fetchone()
			return float(row["daily_kwh"] or 0) if row else 0.0

	def get_meters_consumption(self, rtu_id: int, calc_date: date) -> Dict[str, Any]:
		"""
		获取RTU下所有在线电表的用电量（从DC_METER_FREEZE表）

		Args:
			rtu_id: RTU设备ID
			calc_date: 计算日期

		Returns:
			Dict: 包含 meter_count, valid_meter_count, total_consumption
		"""
		sql = """
			SELECT DISTINCT
				m.id,
				m.serial_no,
				r.serial_no AS rtu_serial_no,
				m.status,
				f.daily_kwh
			FROM dev_meter m
		    LEFT JOIN dev_rtu r ON m.rtu_id = r.id
			LEFT JOIN dc_meter_freeze f ON r.serial_no = f.serial_no AND f.freeze_day = %s
			WHERE m.deleted = 0
			  AND (
				  m.id IN (
					  SELECT h.child_meter_id
					  FROM dev_meter_hierarchy h
					  INNER JOIN dev_meter parent ON h.parent_meter_id = parent.id
					  WHERE parent.rtu_id = %s
						AND parent.deleted = 0
				  )
			  )
        """
		with self.conn.cursor(pymysql.cursors.DictCursor) as cursor:
			cursor.execute(sql, (
				calc_date,
				rtu_id
			))
			rows = cursor.fetchall()

		meter_count = len(rows)
		valid_meter_count = sum(1 for r in rows if r["daily_kwh"] and r["daily_kwh"] > 0)
		total_consumption = sum(float(r["daily_kwh"] or 0) for r in rows)

		return {
			"meter_count": meter_count,
			"valid_meter_count": valid_meter_count,
			"total_consumption": total_consumption
		}

	def save_line_loss(
			self,
			rtu_id: int,
			rtu_code: str,
			calc_date: date,
			rtu_consumption: float,
			meters_consumption: float,
			loss_kwh: float,
			loss_rate: float,
			meter_count: int,
			valid_meter_count: int,
			data_quality: str
	):
		"""
		保存线损计算结果

		Args:
			rtu_id: RTU设备ID
			rtu_code: RTU设备编码
			calc_date: 计算日期
			rtu_consumption: RTU供电量
			meters_consumption: 电表总用电量
			loss_kwh: 线损电量
			loss_rate: 线损率
			meter_count: 电表总数
			valid_meter_count: 有效电表数
			data_quality: 数据质量标记
		"""
		sql = """
            INSERT INTO dev_rtu_line_loss (
                calc_date, rtu_id, rtu_code, rtu_consumption, meters_consumption,
                loss_kwh, loss_rate, meter_count, valid_meter_count, data_quality
            ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            ON DUPLICATE KEY UPDATE
                rtu_consumption = VALUES(rtu_consumption),
                meters_consumption = VALUES(meters_consumption),
                loss_kwh = VALUES(loss_kwh),
                loss_rate = VALUES(loss_rate),
                meter_count = VALUES(meter_count),
                valid_meter_count = VALUES(valid_meter_count),
                data_quality = VALUES(data_quality)
        """
		with self.conn.cursor() as cursor:
			cursor.execute(sql, (
				calc_date, rtu_id, rtu_code,
				round(rtu_consumption, 4),
				round(meters_consumption, 4),
				round(loss_kwh, 4),
				round(loss_rate, 2),
				meter_count,
				valid_meter_count,
				data_quality
			))

	def calculate_Master_line_loss(self, rtu: Dict[str, Any]):
		"""
		计算三相表的线损

		Args:
			rtu: 设备信息字典
		"""
		rtu_id = rtu["id"]
		rtu_code = rtu["serial_no"]

		try:
			# 1. 获取三相表当日用电量
			rtu_consumption = self.get_Master_consumption(rtu_code, self.calc_date)

			if rtu_consumption == 0:
				logger.debug(f"RTU {rtu_code} 在 {self.calc_date} 无用电量数据，跳过")
				self.stats["no_data"] += 1
				return

			# 2. 获取三相表下所有电表的日用电量
			meters_data = self.get_meters_consumption(rtu_id, self.calc_date)
			meter_count = meters_data["meter_count"]
			valid_meter_count = meters_data["valid_meter_count"]
			meters_consumption = meters_data["total_consumption"]

			# 3. 计算线损
			loss_kwh = rtu_consumption - meters_consumption
			loss_rate = (loss_kwh / rtu_consumption * 100) if rtu_consumption > 0 else 0

			# 4. 数据质量判断
			data_quality = "NORMAL"
			if valid_meter_count < meter_count * 0.8:
				data_quality = "PARTIAL"
			if loss_rate > 100 or loss_rate < -20:
				data_quality = "ABNORMAL"

			# 5. 保存结果
			self.save_line_loss(
				rtu_id, rtu_code, self.calc_date,
				rtu_consumption, meters_consumption,
				loss_kwh, loss_rate,
				meter_count, valid_meter_count,
				data_quality
			)

			self.conn.commit()
			self.stats["success"] += 1

			logger.info(
				f"RTU {rtu_code} 线损计算完成: "
				f"供电量={rtu_consumption:.2f}kWh, "
				f"售电量={meters_consumption:.2f}kWh, "
				f"线损={loss_kwh:.2f}kWh, "
				f"线损率={loss_rate:.2f}%, "
				f"质量={data_quality}"
			)

		except Exception as e:
			logger.error(f"计算RTU {rtu_code} 线损失败: {e}", exc_info=True)
			self.conn.rollback()
			self.stats["error"] += 1

	@tenacity.retry(
		stop=tenacity.stop_after_attempt(3),
		wait=tenacity.wait_fixed(5),
		retry=tenacity.retry_if_exception_type(Exception)
	)
	def run(self):
		"""主执行流程"""
		from datetime import datetime
		start_time = datetime.now()

		logger.info("=" * 80)
		logger.info(f"线损计算任务开始: {start_time}")
		logger.info("=" * 80)

		try:
			# 计算昨天的线损
			self.calc_date = date.today() - timedelta(days=1)
			logger.info(f"计算日期: {self.calc_date}")

			# 连接数据库
			self.connect_db()

			# 获取所有RTU列表
			rtus = self.get_all_rtus()
			self.stats["total_rtu"] = len(rtus)

			# 逐个计算RTU线损
			for rtu in rtus:
				self.calculate_Master_line_loss(rtu)

			# 统计信息
			end_time = datetime.now()
			duration = (end_time - start_time).total_seconds()

			logger.info("=" * 80)
			logger.info(f"线损计算任务完成: {end_time}")
			logger.info(f"执行时长: {duration:.2f} 秒")
			logger.info(f"统计信息:")
			logger.info(f"  - RTU总数: {self.stats['total_rtu']}")
			logger.info(f"  - 计算成功: {self.stats['success']}")
			logger.info(f"  - 无数据: {self.stats['no_data']}")
			logger.info(f"  - 计算失败: {self.stats['error']}")
			logger.info("=" * 80)

		except Exception as e:
			logger.error(f"任务执行失败: {e}", exc_info=True)
			raise
		finally:
			self.close_db()


if __name__ == '__main__':
	task = LineLossCalculationTask()
	task.run()
